package dto;

import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.awt.image.renderable.ParameterBlock;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;

import javax.imageio.ImageIO;
import javax.media.jai.JAI;
import javax.media.jai.RenderedOp;

import vo.boardVO;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

import db.MySql;

public class boardDTO {

	private boardVO vo = null;
	private Connection conn = null;

	//페이지에 관련된 변수
			int listSize = 10;  //페이지당 표시되는 행수
			int blockSize = 5; //한블럭당 표시되는 페이지 수
			int totSize = 0;   //전체 게시물의 갯수
			int totPage = 0;   //전체 페이지 수
			int totBlock = 0;  //전체 블럭 수
			int nowPage = 0;   //현재 페이지
			int nowBlock = 0;  //현재 블럭
			int startNo = 0;   //리스트 목록의 시작 위치
			int endNo = 0;     //리스트 목록의 끝 위치
			int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
			int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호

	String saveDir = "C:/workspace/group2/WebContent/images/";

	public boardDTO() {
		conn = new MySql().getConn();
	}
	
	public boardDTO(boardVO v){
		this.vo = v;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
		
		conn = new MySql().getConn();
	}
	public void list_size_set(int list_size){
		this.listSize = list_size;
	}

	public void e_num() { // 사원번호를 통해서 이름을 가져옴.
										// (문제해결)반환형을 boardVO로 했더니 값을 못 가져옴.
										// long으로 바꿔줌
//		System.out.println("e_num()메소드가 실행됨 -----");
		long e_num = 0;
		String sql = "select e_number from semyadmin.messenger where m_id = ?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getM_id());
//			System.out.println("들어온 아이디 :" + vo.getM_id());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				e_num = rs.getLong("e_number");
				vo.setE_number(e_num);
				
//				System.out.println("ResultSet으로 가져온 e_num : " + rs.getLong("e_number"));
//				System.out.println("vo에 셋팅된 e_num : " + vo.getE_number());
			}
			rs.close();
			pst.close();
		} catch (Exception e) {
		}
//		System.out.println("did you got my E_number4? " + vo.getE_number());
//		System.out.println("e_num()메소드가 종료됨 ----- e_num 반환");
//		System.out.println("e_num 값 :  " + e_num);
//		System.out.println("--------------------------------------------");
	}

	
	
	public void tempB_num(String m_id) { // 새 글넘버 새성
		String sql = "insert into semyadmin.board value (0,0,'',now(),now(),?,'','','',0,0,0,0,0,0,'')";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
//			System.out.println("B_tempNum에서 m_id값" + vo.getM_id());
			pst.setString(1, vo.getM_id());
			pst.executeUpdate();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	public int initInsert(String m_id) { // (문제해결)autoin_crement로는 다음 값을 가져올 수 없기 때문에먼저 게시판 번호를 넣어줌.				
		int b_num = 0;
		String sql = "select b_number from semyadmin.board where b_title = ?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m_id);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				b_num = rs.getInt("b_number");
				vo.setB_tempNum(b_num);
//				System.out.println("B_tempNum가 추가됐나" + rs.getInt("b_tempNum"));
			}
			rs.close();
			pst.close();
		} catch (Exception e) {

		}
//		System.out.println("vo.getB_tempNum가 추가됐나 :" + vo.getB_tempNum());

		return b_num;
	}	

	
	public boolean insert(boardVO v) { // 글 작성
		//initInsert(v.getB_number()); // (문제해결)b_number를 먼저 만들어놓고 업데이트 하는 방식으로 변경 - 안됨 
									   // (재해결) 그냥 메소드를 따로 부르기로 
		// this.vo = v; // image를 안 불를 때 하려고 잠깐 매개변수를 넣어놓앗슘.
		boolean b = false;
		String sql = "update semyadmin.board set "
				+ " b_number =?, e_number =?, b_category=?, b_date=now(), b_modifyDate=now(), "
				+ " b_title=?, b_contents=?, b_fileUrl=?, b_image=?, b_downNo=0, b_viewNo=0, "
				+ " b_pNumber=0, b_group=?, b_deep=?, b_indent=0, b_ipAddress='yet' where b_number = ?";

		/*
		 * String sql =
		 * "insert into semyadmin.board(e_number, b_category, b_date, b_modifyDate, "
		 * + " b_title, b_contents, b_fileUrl, b_image, b_downNo, b_viewNo, " +
		 * " b_pNumber, b_group, b_deep, b_indent, b_ipAddress) " +
		 * " values(?, ?,now(),now()," + " ?, ?, ?, ?, 0, 0," +
		 * " 0, (select*from (select max(b_number)+1 from semyadmin.board)next),"
		 * +
		 * " (select*from (select max(b_number)+1 from semyadmin.board)next), 0, 'yet')"
		 * ;
		 */
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getB_tempNum()); // 만들어진 임시 넘버를 받음.
			pst.setLong(2, vo.getE_number());
			pst.setInt(3, vo.getB_category());
			pst.setString(4, vo.getB_title());
			pst.setString(5, vo.getB_contents());
			pst.setString(6, vo.getB_fileUrl());
			pst.setString(7, vo.getB_image());
			if(vo.getB_group() != 0){
				pst.setInt(8, vo.getB_group());
			}
			else{
				pst.setInt(8, vo.getB_tempNum());
			}
			pst.setInt(9, vo.getB_deep());
			pst.setInt(10, vo.getB_tempNum());
			/* pst.setString(7, vo.getB_ipAddress()); */// 아직 처리 전
			int cnt = pst.executeUpdate();
			if (cnt > 0)
				b = true;
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	public void page() {
		String sql = "select count(*) cnt from( select * from semyadmin.board where b_title like ? )a where a.b_category = ?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%" + vo.getFind() + "%");
			pst.setInt(2, vo.getB_category());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				totSize = rs.getInt("cnt");

				totPage = (int) Math.ceil(totSize * 1.0 / listSize);
				totBlock = (int) Math.ceil(totPage * 1.0 / blockSize);
				nowBlock = (int) Math.ceil(nowPage * 1.0 / blockSize);

				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize + 1;

				endNo = nowPage * listSize;
				startNo = endNo - listSize + 1;

				if (endPage > totPage)
					endPage = totPage;
				if (endNo > totSize)
					totSize = endNo;
			}
			
//			System.out.println("totSize=" + totSize);
//			
//			System.out.println("totPage=" + totPage);
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("endPage=" + endPage);
//			System.out.println("startPage=" + startPage);
//			
//			System.out.println("endNo=" + endNo);
//			System.out.println("startNo=" + startNo);

			rs.close();
			pst.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<boardVO> list() { // list 처리 + 검색
		ArrayList<boardVO> al = new ArrayList<boardVO>();
//		System.out.println("did you got my E_number? in select"	+ vo.getE_number());
		page();

		String sql = " SELECT * "
				+ " FROM "
				+ "    (SELECT A.* "
				+ "      FROM "
				+ " (SELECT b.*, e.e_name, @RNUM := @RNUM+1 AS RNUM"
				+ "           FROM (SELECT @RNUM := 0) tmp INNER JOIN semyadmin.board b JOIN semyadmin.employees e "
				+ "          ON b.e_number = e.e_number AND b.b_category = ? "
				+ "           WHERE e.e_name LIKE ? OR b.b_title LIKE ? OR b.b_contents LIKE ? "
				+ "           ORDER BY b.b_group DESC, b.b_deep ASC, b.b_number DESC "
				+ "         )A "
				+ "    )B "
				+ " WHERE B.RNUM between ? and ?";

		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getB_category());
			pst.setString(2, "%" + vo.getFind() + "%");
			pst.setString(3, "%" + vo.getFind() + "%");
			pst.setString(4, "%" + vo.getFind() + "%");
			pst.setInt(5, startNo);
			pst.setInt(6, endNo);
//			System.out.println("endNo: " + endNo);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				boardVO v = new boardVO();
				v.setB_number(rs.getInt("b_number"));
				v.setE_number(rs.getLong("e_number"));
				v.setE_name(rs.getString("e_name"));
				v.setB_category(rs.getInt("b_category"));
				v.setB_date(rs.getString("b_date"));
				v.setB_modifyDate(rs.getString("b_modifyDate"));
				v.setB_title(rs.getString("b_title"));
				v.setB_contents(rs.getString("b_contents"));
				v.setB_fileUrl(rs.getString("b_fileUrl"));
				v.setB_image(rs.getString("b_image"));
				v.setB_downNo(rs.getInt("b_downNo"));
				v.setB_viewNo(rs.getInt("b_viewNo"));
				v.setB_ipAddress(rs.getString("b_ipAddress"));
				v.setB_number(rs.getInt("b_number"));
				v.setB_indent(rs.getInt("b_indent"));
				v.setB_group(rs.getInt("b_group"));
				v.setB_deep(rs.getInt("b_deep"));
//				System.out.println("b_number : " + v.getB_number());
				al.add(v);
			}
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return al;
	}

	public boardVO View() { 	// view : 선택한 글 보기 
		boardVO v = new boardVO();
		String sql = "select i.*, j.e_name from semyadmin.board i "
				+ "			join semyadmin.employees j "
				+ "			on i.e_number = j.e_number "
				+ "	 where i.b_number=?";

		try {
			PreparedStatement pst = conn.prepareStatement(sql);
//			System.out.println("\n" + "view의 b_number : " + vo.getB_number());
			pst.setInt(1, vo.getB_number());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				v.setB_number(rs.getInt("b_number"));
				v.setE_number(rs.getLong("e_number"));
				v.setE_name(rs.getString("e_name"));
				v.setB_category(rs.getInt("b_category"));
				v.setB_date(rs.getString("b_date"));
				v.setB_modifyDate(rs.getString("b_modifyDate"));
				v.setB_title(rs.getString("b_title"));
				v.setB_contents(rs.getString("b_contents"));
				v.setB_fileUrl(rs.getString("b_fileUrl"));
				v.setB_image(rs.getString("b_image"));
				v.setB_downNo(rs.getInt("b_downNo"));
				v.setB_viewNo(rs.getInt("b_viewNo"));
				v.setB_ipAddress(rs.getString("b_ipAddress"));
				v.setB_group(rs.getInt("b_group"));
				v.setB_deep(rs.getInt("b_deep"));
			}
//			System.out.println("\n" + "view의 b_title : " + v.getB_title());
//			System.out.println("\n" + "view의 b_modifyDate : "
//					+ v.getB_modifyDate());
			rs.close();
			pst.close();
			
			update_view_num(v.getB_number());
			
			conn.close();
		} catch (Exception e) {

		}
		return v;
	}

	public void update_view_num(int number){
		String sql = "UPDATE semyadmin.board SET "
				+ " b_viewNo =  b_viewNo+1"
				+ "  WHERE b_number = ? ";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, number);
			int cnt = pst.executeUpdate();
			if (cnt > 0)
//				System.out.println("갱신 조회수+1");

			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public boolean delete() {		// 삭제 
									// (문제해결) 삭제하지말고 카테고리값을 변경해서. 다른 게시판으로 옮김) 
		boolean b = false;
		/*String sql = "delete from semyadmin.board where b_number=?";*/
		String sql = "update semyadmin.board set "
				+ " b_category= 0 "
				+ " WHERE b_number = ?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getB_number());
			int cnt = pst.executeUpdate();
//			System.out.println("삭제 이미지 : " + vo.getB_image());
//			System.out.println("삭제 파일 : " + vo.getB_fileUrl());
			if (cnt > 0)
				b = true;

			File image = new File(saveDir + vo.getB_image());
			File file = new File(saveDir + vo.getB_fileUrl());
			if (image.exists())	image.delete();
			if (file.exists())	file.delete();
			
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	public boolean modify() {
		boolean b = false;
		
		String sql = "update semyadmin.board set b_modifyDate=now(), "
					+ " b_title=?, b_contents=?, b_fileUrl=? where b_number = ?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getB_title());
//			System.out.println(vo.getB_title() + vo.getB_contents()+vo.getB_fileUrl()+vo.getB_number());
			pst.setString(2, vo.getB_contents());
			pst.setString(3, vo.getB_fileUrl());
			pst.setInt(4, vo.getB_number());
			int cnt = pst.executeUpdate();
//			System.out.println("들어완1");
			if (cnt > 0)
//				System.out.println("들어완2"); /// 여기가 안됨. 
				b = true;
//				System.out.println("들어완3");
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	public boardVO file(boardVO vo) {	// 이미지, 파일 업로드를 같이함 
		this.vo = vo;

		int size = 1024 * 1024 * 100; // 파일 크기

		String orifile = ""; // 파일
		String sysfile = "";

	/*	String oriImage = ""; // 이미지 (썸네일 겸용)
		String sysImage = "";
*/
		MultipartRequest multi = null;

		try {// (문제해결) MultipartRequest는 한번만 불려야 하므로, 파일과 이미지 업로드를 한 곳에서 처리한다.
			multi = new MultipartRequest(vo.getRequest(), saveDir, size,
					"utf-8", new DefaultFileRenamePolicy());

			Enumeration files = multi.getFileNames();

			String temp01 = (String) files.nextElement(); // 파일
			orifile = multi.getOriginalFileName(temp01);
			sysfile = multi.getFilesystemName(temp01);

			// (문제해결) multitype의 경우 이곳에서 vo에 셋팅을 해야한다. (이후에 input_result로 넘어감)
			
//			System.out.println("시작 " );
			//System.out.println("multi.getParameter (b_tempNum) : " + multi.getParameter("b_tempNum") );
			
			this.vo.setB_title(multi.getParameter("b_title"));
			this.vo.setB_contents(multi.getParameter("b_contents"));
			this.vo.setM_id(multi.getParameter("m_id"));
			this.vo.setE_number(Long.parseLong(multi.getParameter("e_number"))); // 롱형캐스팅																
			this.vo.setB_category(Integer.parseInt(multi.getParameter("b_category")));
			this.vo.setB_group(Integer.parseInt(multi.getParameter("b_group")));
			this.vo.setB_deep(Integer.parseInt(multi.getParameter("b_deep")));
			this.vo.setB_fileUrl(multi.getParameter("b_fileUrl"));
			//this.vo.setB_number(Integer.parseInt(multi.getParameter("b_number")));
			
			/**/
			tempB_num(this.vo.getM_id());
			this.vo.setB_tempNum(initInsert(this.vo.getM_id())); // tempnum을 vo에 추가
			/**/
			//vo.setB_tempNum(Integer.parseInt(multi.getParameter("b_tempNum")));
			
//			System.out.println("b_tempNum :" + this.vo.getB_tempNum());
//			System.out.println("b_title : "+multi.getParameter("b_title"));
//			System.out.println("b_contents : "+multi.getParameter("b_contents"));
//			System.out.println("m_id : "+multi.getParameter("m_id"));
//			System.out.println("e_number : "+Long.parseLong(multi.getParameter("e_number")));			
//			System.out.println("b_group : "+Integer.parseInt(multi.getParameter("b_group")));	
//			System.out.println("b_deep : "+Integer.parseInt(multi.getParameter("b_deep")));	

			// 파일 셋팅
			if (sysfile == null || sysfile.equals("")) {
				this.vo.setB_fileUrl(multi.getParameter("b_orifile"));
			} else {
				this.vo.setB_fileUrl(sysfile);

				ParameterBlock pd = new ParameterBlock();
				pd.add(saveDir + sysfile);
			}
//
//			// 이미지 셋팅
//			if (sysImage == null || sysImage.equals("")) {
//				this.vo.setB_image(multi.getParameter("b_oriImage"));
//			} else {
//				this.vo.setB_image(sysImage);
//
//				ParameterBlock pd = new ParameterBlock();
//				pd.add(saveDir + sysImage);
//				RenderedOp rOp = JAI.create("fileload", pd);
//				BufferedImage bi = rOp.getAsBufferedImage();
//				BufferedImage thumb = new BufferedImage(100, 100,
//						BufferedImage.TYPE_INT_RGB);
//				Graphics2D g = thumb.createGraphics();
//				g.drawImage(bi, 0, 0, 100, 100, null);
//
//				File image = new File(saveDir + "sm_" + sysImage);
//				ImageIO.write(thumb, "png", image);
//			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return this.vo;
	}

	public boolean replyInput() { // (문제해결) 글번호를 먼저 만들어준 후에 update로 넣어준다.
									// (insert와 같은 방법)
		// initInsert(vo.getB_number()); // 답변을 달 때도 먼저 글번호를 만들어준다.
										 // (재해결) 그냥 메소드를 따로 부르기로 
		boolean b = false;

		String sql = "update semyadmin.board set"
				+ " b_number =?, e_number =?, b_category=?, b_date=now(), b_modifyDate=now(), "
				+ " b_title=?, b_contents=?, b_fileUrl=?, b_image=?, b_downNo=0, b_viewNo=0, "
				+ " b_pNumber=?, b_group=?, "
				+ " b_deep= (? || '-' || ? ), b_indent= ?+1, b_ipAddress='yet' ";

		/*
		 * String sql =
		 * "insert into semyadmin.board(e_number, b_category, b_date, b_modifyDate, "
		 * + " b_title, b_contents, b_fileUrl, b_image, b_downNo, b_viewNo, " +
		 * " b_pNumber, b_group, b_deep, b_indent, b_ipAddress) " +
		 * " values(?, ?,now(),now()," + " ?, ?, ?, ?, 0, 0," + " ?, ?," +
		 * " ? || '-' || (select*from (select max(b_number)+1 from semyadmin.board)next), ?+1, 'yet')"
		 * ;
		 */
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getB_tempNum()); // 만들어진 임시 글넘버를 셋팅함.
			pst.setLong(2, vo.getE_number());
			pst.setInt(3, vo.getB_category());
			pst.setString(3, vo.getB_title());
			pst.setString(4, vo.getB_contents());
			pst.setString(5, vo.getB_fileUrl());
			pst.setString(6, vo.getB_image());
			pst.setInt(7, vo.getB_number()); // 답글을 달 실제 글의 넘버 (부모글)
			pst.setInt(8, vo.getB_group());
			pst.setInt(9, vo.getB_group());
			pst.setInt(10, vo.getB_tempNum());
			pst.setInt(11, vo.getB_indent());
			// pst.setString(12, vo.getB_ipAddress()); // 아직 처리 전

			int cnt = pst.executeUpdate();
			if (cnt > 0)
				b = true;

			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	public boardVO getVo() {
		return vo;
	}

	public void setVo(boardVO vo) {
		this.vo = vo;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

}
